USE [OctoNetApps]
GO

/****** Object:  StoredProcedure [classified].[ClassifiedAds_GetMyExpired]    Script Date: 05/25/2011 06:56:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [classified].[ClassifiedAds_GetMyExpired]
	@ResourceID bigint,
	@UserID bigint,
	@AdType tinyint,
	@StartIndex int,
	@AdsOnPage int
AS
BEGIN

	SET NOCOUNT ON;
	DECLARE @RowCnt int;
	IF (@AdType <> 2)
	BEGIN
		SELECT COUNT(*) as cnt, status FROM classified.ClassifiedAds
		WHERE ResourceID = @ResourceID AND UserID = @UserID AND Searching = @AdType
		GROUP BY status
		
		
		SELECT COUNT(*) as cnt FROM classified.ExpiredClassifiedAds
		WHERE ResourceID = @ResourceID AND UserID = @UserID AND Searching = @AdType
		
		
		SET @RowCnt = @StartIndex+@AdsOnPage;
		
		SET ROWCOUNT @RowCnt;

		WITH AdsOrdered AS (
			SELECT AdID, CategoryID, [PostTime],[Title], ROW_NUMBER() OVER (ORDER BY PostTime DESC) as rn
				FROM classified.ExpiredClassifiedAds
			WHERE ResourceID = @ResourceID AND UserID = @UserID AND Searching = @AdType 
		
		) SELECT AdID, CategoryID, [PostTime],[Title] FROM AdsOrdered WHERE rn>@StartIndex  AND rn<=@RowCnt
	END
	ELSE
	BEGIN
		SELECT COUNT(*) as cnt, status FROM classified.ClassifiedAds
		WHERE ResourceID = @ResourceID AND UserID = @UserID 
		GROUP BY status
		
		
		SELECT COUNT(*) as cnt FROM classified.ExpiredClassifiedAds
		WHERE ResourceID = @ResourceID AND UserID = @UserID 

		SET @RowCnt = @StartIndex+@AdsOnPage;
		
		SET ROWCOUNT @RowCnt;

		WITH AdsOrdered AS (
			SELECT AdID, CategoryID, [PostTime],[Title], ROW_NUMBER() OVER (ORDER BY PostTime DESC) as rn
				FROM classified.ExpiredClassifiedAds
			WHERE ResourceID = @ResourceID AND UserID = @UserID 
		) SELECT AdID, CategoryID, [PostTime],[Title] FROM AdsOrdered WHERE rn>@StartIndex  AND rn<=@RowCnt
	END
	SET ROWCOUNT 0;

END







GO


